﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using BookStore.Common;
using System.Data.SqlClient;
using BookStore.Models;
using System.Data;

namespace BookStore.Controllers
{
    public class ReceiptController : Controller
    {
        private static ReceiptController _controller = null;
        public static ReceiptController getController()
        {
            if (_controller == null)
                return new ReceiptController();
            else
                return _controller;
        }
        public ReceiptController()
        {
            _controller = this;
        }

        internal int createReceipt(int staffIDString, string storingdateString)
        {
            int ID = -1;
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("BS_CreateReceipt", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = staffIDString;
                cmd.Parameters.Add("@StoringDate", SqlDbType.Date).Value = storingdateString;
                SqlParameter returnValue = new SqlParameter("@lastID", SqlDbType.Int);
                returnValue.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(returnValue);
                cmd.ExecuteNonQuery();
                ID = (int)returnValue.Value;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return ID;
        }

        internal DataTable getReceiptDetailFromReceipt(String ReceiptID)
        {
            DataTable dt = new DataTable("GetReceiptDetailFromReceipt");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_GetReceiptDetailFromReceipt", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ReceiptID", SqlDbType.Int).Value = ReceiptID;
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        internal DataTable getReceiptInDateData(DateTime datefrom, DateTime dateto)
        {
            DataTable dt = new DataTable("getReceiptInDateData");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_GetReceiptInDate", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@DateFrom", SqlDbType.Date).Value = datefrom;
                cmd.Parameters.Add("@DateTo", SqlDbType.Date).Value = dateto;
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public Boolean CheckReceiptInDate(String storingDate)
        {
            DataTable dt = new DataTable("CheckBReceiptInDate");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_CheckReceiptInDate", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@StoringDate", SqlDbType.Date).Value = storingDate;
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                if (dt.Rows.Count > 0)
                    return true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return false;
        }

        public void InsertTotalOfReceipt(String ReceiptID, String totalofreceipt)
        {
            String sql = @"Update RECEIPT Set TotalOfReceipt = " + totalofreceipt + "Where ReceiptID = " + ReceiptID;
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand(sql, con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public DataTable getStatisticReceiptMonth(int month, int year)
        {
            DataTable dt = new DataTable("GetStatisticReceipt");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_GetStatisticReceiptMonth", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Month", SqlDbType.Int).Value = month;
                cmd.Parameters.Add("@Year", SqlDbType.Int).Value = year;
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public DataTable getStatisticReceiptYear(int year)
        {
            DataTable dt = new DataTable("GetStatisticReceiptYear");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_GetStatisticReceiptYear", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Year", SqlDbType.Int).Value = year;
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public Decimal getTotalOfReceiptInDate(DataGridView dgv)
        {
            Decimal totalofreceipt = 0;
            if (dgv.Rows.Count != 0)
            {
                for (int i = 0; i < dgv.Rows.Count; i++)
                {
                    Decimal receipt = (Decimal)dgv.Rows[i].Cells[2].Value;
                    totalofreceipt += receipt;
                }
            }
            return totalofreceipt;
        }

        public String getNumOfReceipt(DataGridView dgv)
        {
            if (dgv.Rows.Count != 0)
                return dgv.Rows.Count.ToString();
            else
                return "0";
        }

        public DataSet ExportToCrystalReport(String ReceiptID)
        {
            DataSet ds = new DataSet();
            con = BSCommon.getConnection();
            String sql = "SELECT * FROM RECEIPT WHERE RECEIPTID =" + ReceiptID;
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                da.Fill(ds, "RECEIPT");
                sql = "SELECT * FROM STAFF WHERE STAFFID =" + ds.Tables["RECEIPT"].Rows[0]["STAFFID"].ToString();
                da.SelectCommand.CommandText = sql;
                da.Fill(ds, "STAFF");
                sql = "SELECT * FROM RECEIPTDETAIL WHERE RECEIPTID =" + ReceiptID;
                da.SelectCommand.CommandText = sql;
                da.Fill(ds, "RECEIPTDETAIL");
                sql = "SELECT * FROM PRODUCT";
                da.SelectCommand.CommandText = sql;
                da.Fill(ds, "PRODUCT");
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return ds;
        }
    }
}
